package merlin.dao;

import java.util.*;
import java.sql.*;

import merlin.beans.*;

public class ParentSubsidiaryDetailsDAO implements java.io.Serializable{

	Connection con;
	public ParentSubsidiaryDetailsDAO(Connection con){
		this.con = con;
	}



	public ParentSubsidiaryDetailsBean selectParentSubsidiaryDetailsByPK(String pk)throws SQLException{

		PreparedStatement pstmt = con.prepareStatement("SELECT COMPANY_NUMBER,COMPANY_NAME,NET_ASSETS,TURNOVER,SUBSIDIARY_REGISTRATION_NUMBER,PARENT_COMPANY_INDICATOR FROM PARENT_SUBSIDIARY_DETAILS WHERE COMPANY_NUMBER = ? ");
		pstmt.setString(1,pk);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			ParentSubsidiaryDetailsBean bn = new ParentSubsidiaryDetailsBean();
			bn.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bn.setCompanyName(rs.getString("COMPANY_NAME"));
			bn.setNetAssets(rs.getString("NET_ASSETS"));
			bn.setTurnover(rs.getString("TURNOVER"));
			bn.setSubsidiaryRegistrationNumber(rs.getString("SUBSIDIARY_REGISTRATION_NUMBER"));
			bn.setParentCompanyIndicator(rs.getString("PARENT_COMPANY_INDICATOR"));
			return bn;
		}else{
			return null;
		}
	}

	public DbResultSet selectParentSubsidiaryDetailsRecordsByCompanyNumber(String companyNumber)throws SQLException{

		ArrayList records = new ArrayList();
		PreparedStatement pstmt = con.prepareStatement("SELECT COMPANY_NUMBER, SUBSIDIARY_REGISTRATION_NUMBER, COMPANY_NAME   || ' ' || case PARENT_COMPANY_INDICATOR  when '0' then '(S)' when '1' then '(P)' when '2' then '(UP)' end  as COMPANY_NAME, NET_ASSETS, TURNOVER, PARENT_COMPANY_INDICATOR FROM PARENT_SUBSIDIARY_DETAILS WHERE COMPANY_NUMBER = ? ORDER BY PARENT_COMPANY_INDICATOR DESC, COMPANY_NAME ASC");
		pstmt.setString(1,companyNumber);
		ResultSet rs =  pstmt.executeQuery();
		while(rs.next()){
			ParentSubsidiaryDetailsBean bn = new ParentSubsidiaryDetailsBean();
			bn.setCompanyNumber(rs.getString("COMPANY_NUMBER"));
			bn.setCompanyName(rs.getString("COMPANY_NAME"));
			bn.setNetAssets(rs.getString("NET_ASSETS"));
			bn.setTurnover(rs.getString("TURNOVER"));
			bn.setSubsidiaryRegistrationNumber(rs.getString("SUBSIDIARY_REGISTRATION_NUMBER"));
			bn.setParentCompanyIndicator(rs.getString("PARENT_COMPANY_INDICATOR"));
			records.add(bn);
		}
		rs.close();
		pstmt.close();
		if(!records.isEmpty())return new DbResultSet(records,records.size()); else return new DbResultSet();
	}
}